/**
 * ----------------------------------------------------------------------------
 * Copyright Jens Konopka
 * 
 * Alle Rechte vorbehalten.
 * Licence: Apache 2.0
 * ----------------------------------------------------------------------------
 *
 * created on 02.03.2012 by jkonopka
 */
package de.sql.toolkit.provider;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import de.sql.toolkit.Resource;
import de.sql.toolkit.manager.ConnectionManager;
import de.sql.toolkit.model.Column;
import de.sql.toolkit.util.SqlUtil;

/**
 * @author jkonopka
 * 
 */
public class ColumnProvider implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = -1208234822078186653L;
	public static final String COMMENT = "comment";
	public static final String TYPE = "type";

	/**
	 * @param schema
	 * @param table
	 * @return
	 * @throws Exception
	 */
	public List<Column> select(final String schema, final String table) throws Exception {
		final List<Column> list = new ArrayList<Column>();
		final Connection connection = ConnectionManager.getConnection();
		ResultSet result = null;
		Statement statement = null;

		try {

			final StringBuilder sql = new StringBuilder();
			sql.append(Resource.getSqlSpecProp("sql.statement.schema.columns"));
			sql.append(" ");

			if (schema != null || table != null) {
				sql.append("WHERE ");
			}

			if (schema != null) {
				sql.append(Resource.getSqlSpecProp("sql.table.schema"));
				sql.append(" = '");
				sql.append(schema);
				sql.append("'");
				sql.append(" AND ");
			}

			if (table != null) {
				sql.append(Resource.getSqlSpecProp("sql.table.name"));
				sql.append(" = '");
				sql.append(table);
				sql.append("'");
			}

			if (table == null) {
				sql.delete(sql.lastIndexOf(" AND "), sql.length());
			}

			statement = connection.createStatement();
			result = statement.executeQuery(sql.toString());
			while (result.next()) {
				final Column entity = new Column();
				entity.setCatalog(result.getString(Resource.getSqlSpecProp("sql.column.catalog")));
				entity.setSchema(result.getString(Resource.getSqlSpecProp("sql.column.schema")));
				entity.setTable(result.getString(Resource.getSqlSpecProp("sql.column.table")));
				entity.setName(result.getString(Resource.getSqlSpecProp("sql.column.name")));
				entity.setType(result.getString(Resource.getSqlSpecProp("sql.column.datatype")));
				entity.setNullable(result.getBoolean(Resource.getSqlSpecProp("sql.column.isnullable")));
				list.add(entity);
			}

		} finally {
			SqlUtil.close(statement);
			SqlUtil.close(result);
		}

		return list;
	}

}
